package com.weddingee.dao.home;
import com.weddingee.dao.base.BaseDao;
import com.weddingee.exception.DBException;
import com.weddingee.exception.DaoException;
import com.weddingee.model.News;
import com.weddingee.model.Other;
import com.weddingee.model.User;
import com.weddingee.util.cond.UserCondition;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

public class OtherDao extends BaseDao{
	
	
	//save
	public Other save(Other other){
		Connection conn = null;
		PreparedStatement pstm  = null;
			try {
				conn = getConn();
				String sql = "insert into other values(null,?,?,?,?,?,?,?,?,?,?,?)";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				//NAME
				pstm.setString(1,  other.getOthername());
				//URL
				pstm.setString(2,  other.getUrl());
				//Type
				pstm.setString(3, other.getType());
				//MESSAGE1
				pstm.setString(4, other.getMessage1());
				//MESSAGE2
				pstm.setString(5, other.getMessage2());
				//MESSAGE3
				pstm.setString(6, other.getMessage3());
				//MESSAGE4
				pstm.setString(7, other.getMessage4());
				//MESSAGE5
				pstm.setString(8, other.getMessage5());
				//MESSAGE6
				pstm.setString(9, other.getMessage6());	
				//MESSAGE7
				pstm.setString(10, other.getMessage7());	
				//introduce
				pstm.setString(11, other.getIntroduce());	
				int res = pstm.executeUpdate();
				if( res > 0){
					ResultSet rset = pstm.getGeneratedKeys();
					rset.next();
					other.setOtherId(rset.getLong(1));
					return other;
				}
				
				return null;
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error");
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
	}
	


	 
	 //list
	 public List<Other> listOther(int page,String otherType){
		 Connection conn = null;
			PreparedStatement pstm  = null;
			
			 List<Other> otherList = new ArrayList<Other>();
				try {
					conn = getConn();
					String sql = "SELECT * FROM other where TYPE = ? order by id asc LIMIT ?,54";
					pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
					pstm.setString(1, otherType);
					pstm.setInt(2,  (page-1)*54);
					ResultSet rset = pstm.executeQuery();
					int i=0;
					while(rset.next())
					{
					Other other = new Other();
					other.setOtherId(rset.getLong(1));
					other.setOthername(rset.getString(2));
					other.setUrl(rset.getString(3));
					other.setType(rset.getString(4));
					other.setMessage1(rset.getString(5));
					other.setMessage2(rset.getString(6));
					other.setMessage3(rset.getString(7));
					other.setMessage4(rset.getString(8));
					other.setMessage5(rset.getString(9));
					other.setMessage6(rset.getString(10));
					other.setMessage7(rset.getString(11));
					other.setIntroduce(rset.getString("INTRODUCE"));
					otherList.add(i,other);
					i++;
					}
				    return otherList;
					
					
				} catch (Exception e) {
					e.printStackTrace();
					throw new DaoException("error",e);
				} finally{
					try {
						closeStatement(pstm);
						closeConn(conn);
					} catch (DBException e) {
						e.printStackTrace();
					}
				}
			
			
			
	  }
	 
	 //find
	 public Other find(Long otherId){
		 Connection conn = null;
			PreparedStatement pstm  = null;
			Other other = new Other();
				try {
					conn = getConn();
					String sql = "select * from other where id= ?";
					pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
					
					//newsId
					pstm.setLong(1, otherId);
					
					
					ResultSet rset = pstm.executeQuery();
					rset.next();
					other.setOtherId(otherId);
					other.setOthername(rset.getString(2));
					other.setUrl(rset.getString(3));
					other.setType(rset.getString(4));
					other.setMessage1(rset.getString(5));
					other.setMessage2(rset.getString(6));
					other.setMessage3(rset.getString(7));
					other.setMessage4(rset.getString(8));
					other.setMessage5(rset.getString(9));
					other.setMessage6(rset.getString(10));
					other.setMessage7(rset.getString(11));
					other.setIntroduce(rset.getString("INTRODUCE"));
					return other;
					
					
				} catch (Exception e) {
					e.printStackTrace();
					throw new DaoException("error",e);
				} finally{
					try {
						closeStatement(pstm);
						closeConn(conn);
					} catch (DBException e) {
						e.printStackTrace();
					}
				}
			
	 }
	 
	  //delete
	  public String delete(Long otherId){
		  Connection conn = null;
			PreparedStatement pstm  = null;
				try {
					conn = getConn();
					String sql = "delete from other where id= ?";
					pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
					
					//newsId
					pstm.setLong(1, otherId);
					int res = pstm.executeUpdate();
					return null;
				} catch (Exception e) {
					e.printStackTrace();
					throw new DaoException("error");
				} finally{
					try {
						closeStatement(pstm);
						closeConn(conn);
					} catch (DBException e) {
						e.printStackTrace();
					}
				}
		}
	
	//update
		public Other update (Other other){
			Connection conn = null;
			PreparedStatement pstm  = null;
				try {
					conn = getConn();
					String sql = "update other set NAME=? , URL=? , TYPE=? , PROFILE_1=?,PROFILE_2=?,PROFILE_3=?,PROFILE_4=?,PROFILE_5=?,PROFILE_6=?,PROFILE_7=?,INTRODUCE=?  where id= ?";          
					pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
					//NAME
					pstm.setString(1,  other.getOthername());
					//URL
					pstm.setString(2,  other.getUrl());
					//Type
					pstm.setString(3, other.getType());
					//MESSAGE1
					pstm.setString(4, other.getMessage1());
					//MESSAGE2
					pstm.setString(5, other.getMessage2());
					//MESSAGE3
					pstm.setString(6, other.getMessage3());
					//MESSAGE4
					pstm.setString(7, other.getMessage4());
					//MESSAGE5
					pstm.setString(8, other.getMessage5());
					//MESSAGE6
					pstm.setString(9, other.getMessage6());		
					//MESSAGE7
					pstm.setString(10, other.getMessage7());	
					//INTRODUCE
					pstm.setString(11, other.getIntroduce());
					//id
					pstm.setLong(12, other.getOtherId());
					int res = pstm.executeUpdate();
					return other;

				} catch (Exception e) {
					e.printStackTrace();
					throw new DaoException("error");
				} finally{
					try {
						closeStatement(pstm);
						closeConn(conn);
					} catch (DBException e) {
						e.printStackTrace();
					}
				}
		}

		/**
		 * cond.type类型用户的数量
		 * @param cond
		 * @return
		 */
		public int listOtherTotalCount(String type) {
			int totalCount = 0;
			Connection conn = null;
			PreparedStatement pstm = null;
			try {
				List<User> userList = new ArrayList<User>();
				conn = getConn();
				String sql = null;
				if( type == null ){
					sql = "SELECT count(*) FROM other AS _u";
				}else{
					sql = "SELECT count(*) FROM other AS _u WHERE _u.TYPE=?";
				}
				
				pstm = conn.prepareStatement(sql);
				
				if( type == null ){
					//do nothing
				}else{
					pstm.setString(1,type);
				}
				
				ResultSet rs = pstm.executeQuery();
				
				while (rs.next()) {
					totalCount = rs.getInt(1);
				}
				
				return totalCount;

			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error", e);
			} finally {
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
		}	
}